Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# MONITOR

The MONITOR command records data on the performance cost of each line in a specified program. To get meaningful information from MONITOR, your session must be the only one running in Oracle OLAP. Furthermore, the accuracy of the results of MONITOR decreases as more processes are started on the host computer.

You first use a MONITOR statement to specify a program for monitoring; then you run the program and use MONITOR again to obtain the results. When the program executes a given line repeatedly, MONITOR records the cumulative cost of all the executions on the single line of its monitor list that is devoted to that program line.

A line of code is considered to have a high performance cost when it takes a long time to execute. Use a TRACKPRG statement to identify programs that have relatively high costs and then use MONITOR to identify the time-consuming lines within those programs. When you wish, you can use both TRACKPRG and MONITOR simultaneously.

Syntax

MONITOR ON [programs] | OFF | INIT | FILE [[APPEND] file-name] | RESET

where programs is one of the following:

  ALL [awlist]
  program-name

Arguments

ON

  Starts looking for the specified programs to be run so that Oracle OLAP can gather line-by-line timing data in a monitor list. (It continues the current monitoring process without interruption when monitoring is already on, or resumes with a gap when monitoring was off.)

  When you do not specify a value for programs, the default is the program or programs specified in the last executed `MONITOR` `ON` statement. When there was no such statement in your current session, no data is collected and no error is produced.

ALL

  Specifies that all of the programs in one or more analytic workspaces are monitored.

awlist

  The name of one or more analytic workspaces (optionally separated by commas) whose programs you want monitored. When you omit this argument, Oracle OLAP monitors all of the programs in all of the attached analytic workspaces

program-name

  The name of a specific program that you want monitored.

OFF

  Stops monitoring programs and freezes any timing data currently in the monitor list. This lets you immediately, or later in your session, send the list to the current outfile or to a text file.

RESET

  (Useful only when monitoring is on.) Retains information about the programs that ares currently specified for monitoring and the Oracle OLAP memory that is allocated for the current monitor list, but discards any timing data currently in the list. In addition, RESET causes MONITOR to again begin waiting for you to run the same programs. When you do, MONITOR automatically gathers new timing data into a new monitor list for the same programs in the same memory allocation as before.

INIT

  (Useful only when monitoring is on.) Initializes the monitoring environment. Initialization consists of discarding the program names and the timing data associated with the current monitor list, and releasing the Oracle OLAP memory previously used for that list so it can be used for other purposes or for collecting new data in a new monitor list.

FILE

  Specifies that the timing data that is currently in the monitor list is sent to a file.

APPEND

  Specifies that the timing data is appended to an existing file. When you omit this argument, the new output replaces the current contents of the file.

file-name

  A text expression that is the name of the file to receive the output. Unless the file is in the current directory, you must include the name of the directory object in the name of the file. When file-name is specified, the data is sent to the named text file. FILE has no effect on the monitor list, so you can send the same list repeatedly to different destinations. When file-name is omitted, Oracle OLAP sends the timing data that is currently in the monitor list to the current outfile.

  Note: Directory objects are defined in the database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

Notes

Monitor List

Each entry (that is, line) in the monitor list focuses on the execution of a single program line, regardless of how many times it is executed. Each entry is divided into the following four sections:

- Cumulative total time of all executions in seconds, in columns 1 through 11

- Number of times executed, in columns 12 through 18

- Line number, in columns 19 through 23

- Text of the line, in column 24 and subsequent columns

Here is a sample of MONITOR output with column numbers included for reference.

    12345678901234567890123456789012345678901234567890

    60              1    1 push name
    30              1    2 trap on GETOUT noprint
    51              1    3 limit name to obj(type) eq 'DIMENSION'
    0               1    4 for name
    0               0    5    do
    450             6    6    limit &name to ALL ifnone BYPASS
    0               0    7 BYPASS:
    0               0    8    doend
    0               0    9 GETOUT:
    0               1   10 pop name

The following is the full description of the program used for the preceding output. Note that in the output, the line with the LIMIT command is truncated because it is too long to fit.

    DEFINE allstat PROGRAM
    LD Program to set the status of all dimensions in the analytic workspace to ALL
    PROGRAM
    PUSH NAME
    TRAP ON getout NOPRINT
    LIMIT NAME TO OBJ(TYPE) EQ 'Dimension' IFNONE getout
    FOR NAME
       DO
       LIMIT &NAME TO ALL IFNONE bypass
    bypass:
       DOEND
    getout:
    POP NAME
    END

Attaching, Detaching, and Reattaching Analytic Workspaces

When Oracle OLAP executes a program in an analytic workspace that has been attached, detached, a new block of data is collected.

Truncated Statement Lines

When a program line is too long, the MONITOR output truncates it. Continuation lines do not appear in the output.

Producing a Report

When you want to produce an Oracle OLAP report from the timing data in the MONITOR file, you can write a program that uses a FILEREAD statement to read the data into an Oracle OLAP variable, and then use Oracle OLAP reporting capabilities to produce a report. You can use the `TRACKREPORT` program as a model. However, keep in mind that the `TRACKREPORT` program was written to produce a report on TRACKPRG output, not MONITOR output.

Bracketing Lines

When you just want to time a particular line or group of lines in a program, you can insert `MONITOR` `ON` and `MONITOR` `OFF` statements in the program to bracket just the line or lines in which you are interested.

Using MONITOR for a Nested Programs

When you do not want to run a nested program by itself, you can specify its name in a `MONITOR` `ON` statement and then run the program that calls it. MONITOR will gather timing data only for the specified (nested) program. When the specified program is called more than once, for each program line, MONITOR will accumulate the total seconds taken by all the times the line was run and provide the number of times it was run.

When you just want to time a particular execution of a nested program that is called more than once, you can insert `MONITOR` `ON` and `MONITOR` `OFF` statements in the calling program to bracket the single call in which you are interested.

Using MONITOR with Very Small Programs

You might not be able to reproduce the results exactly for very small programs. When the CPU interrupts processing to do other tasks, that time is a greater percentage of the total execution time.

Unit of Measure for MONITOR

The MONITOR and TRACKPRG commands use milliseconds as the unit for recording execution time. The execution time does not include time spent on I/O and time spent waiting for the next statement.

Examples

Example 18-1 Collecting Timing Data Using MONITOR

In this example, MONITOR is used to collect timing data on the execution of the individual lines of code in `prog1` and then to send the data to a text file. The `MONITOR` `ON` statement is then used to discard the `prog1` timing data and start collecting data on `prog2`. After the data for `prog2` is sent to a second file, `MONITOR` `INIT` is used to discard the current monitor list and release the memory used for it.

    MONITOR ON prog1
    prog1
    MONITOR FILE prog1.mon
    MONITOR ON prog2
    prog2
    MONITOR OFF
    MONITOR FILE prog2.mon
    MONITOR INIT